if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCOIUsersForNotification]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCOIUsersForNotification]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/******************************************************************                
* Name:   ExecuteCOINotification                
* Purpose:  Notify/Remind users to fill out COIs                
*                
* PARAMETERS                
* Name        Description                     
* -------------     -------------------------------------------                
*  @type               Type of notification      
*  @institutionId      Institution under which the users have to be notified   
*  @lastActivityDate   Last activity date(for open enrollment)            
******************************************************************/                
                
CREATE Procedure [dbo].[GetCOIUsersForNotification]       
(                
 @type Varchar(50),              
 @institutionId int = 0,              
 @lastActivityDate Datetime = NUll,
 @reportingPeriodId int              
)                
AS                
BEGIN                
                
 SET NOCOUNT ON                
 --Create the temporary table to hold the list of users having having access to COI               
 DECLARE @TempPersonIds TABLE (                
 [RowNum] INT IDENTITY (1,1),                
 [Id] INT,
 [AddCOI] Bit                
 )                
 
Insert into @tempPersonIds Exec dbo.ExecuteCOIOpenEnrollment @institutionId,@lastActivityDate,@type, @reportingPeriodId 
   
declare @email varchar(100)
execute @email = dbo.[GetServerEMail]

DECLARE @TempPersonEmail TABLE (                
 [RowNum] INT IDENTITY (1,1),                
 [EmailAddress] varchar(250),
 [FullName]varchar(300),
 [AdmPersonId] int,
 [AddCOI] bit
 )     
		Insert into @TempPersonEmail SELECT ISNULL(@email, E.EmailAddress), 
		rtrim(ltrim(adp.LastName))+', '+rtrim(ltrim(adp.FirstName))  as FullName,
		adp.Id as AdmPersonId, tp.AddCOI
		  FROM AdmEmailAddress E 
			INNER JOIN AdmEmailAddressType ET 
				ON E.AdmEmailAddressTypeId = ET.Id
		  	INNER JOIN AdmPerson adp 
				ON adp.Id = e.admPersonId
			INNER JOIN @tempPersonIds tp on tp.Id = e.AdmPersonId
		  WHERE ET.Name = 'Primary'                

Select distinct(EmailAddress),FullName, AdmPersonId, AddCOI from  @TempPersonEmail where EmailAddress like '%@%.%' 
Select distinct(EmailAddress),FullName, AdmPersonId, AddCOI from  @TempPersonEmail where EmailAddress not like '%@%.%'
                         
INSERT INTO COIBatchPerson (EmailAddress, FullName, AdmPersonId, AddCOI) 
Select distinct(EmailAddress),FullName, AdmPersonId, AddCOI from  @TempPersonEmail where EmailAddress like '%@%.%' 
INSERT INTO COIBatchPerson (EmailAddress, FullName, AdmPersonId, AddCOI) 
Select distinct(EmailAddress),FullName, AdmPersonId, AddCOI from  @TempPersonEmail where EmailAddress not like '%@%.%'

END         
